IF exists(SELECT * FROM dbo.sysobjects WHERE name='WEB_loopbaan' AND xtype='P') 
BEGIN
	DROP PROCEDURE WEB_loopbaan
END
GO

CREATE   procedure WEB_loopbaan
(@iNr char(7))
as
begin
set nocount on

--Voor de performantie vullen we enkele hulptabellen:
--De resultaten van testen, waarschuwingen en ..
select * into #deelresultaatBijkomend
from resultaatBijkomend 
where inschrijvingsNr = @iNr

select * into #deelresultaatOvergang
from resultaatOvergang
where inschrijvingsNr = @iNr

select * into #deelresultaatTaak
from resultaatTaak
where inschrijvingsNr = @iNr

select * into #deelresultaatWaarschuwing
from resultaatWaarschuwing
where inschrijvingsNr = @iNr

-- Overzicht
create table #taakTest
	(jaar char(4),
	vak char(2),
	tekst varchar(50))

insert into #taakTest(jaar,vak)
select jaar,vak from #deelresultaatWaarschuwing union
select jaar,vak from #deelresultaatTaak union
select jaar,vak from #deelresultaatBijkomend	union 
select jaar,vak from #deelresultaatOvergang

update #taakTest
set tekst = 'test="taak"'
	from #taakTest as t 
	right join #deelresultaatTaak as d
	on t.jaar = d.jaar and t.vak = d.vak

update #taakTest
set tekst = 'test="waarschuwing"'
	from #taakTest as t 
	right join #deelresultaatWaarschuwing as d
	on t.jaar = d.jaar and t.vak = d.vak

update #taakTest
set tekst = 'test="overgang"'
	from #taakTest as t 
	right join #deelresultaatOvergang as d
	on t.jaar = d.jaar and t.vak = d.vak

update #taakTest
set tekst = 'test="bijkomend"'
	from #taakTest as t 
	right join #deelresultaatBijkomend as d
	on t.jaar = d.jaar and t.vak = d.vak

--De cijfers

select inschrijvingsNr,jaar,klas,vakcode,vakvolg,
	case when dw1 >= 0 then convert(varchar(3),dw1) else '' end as dw1, 
	case when ex1 >= 0 then convert(varchar(3),ex1) else '' end as ex1, 
	case when tot1 >= 0 then convert(varchar(3),tot1) else '' end as tot1, 
	case when dw2 >= 0 then convert(varchar(3),dw2) else '' end as dw2, 
	case when ex2 >= 0 then convert(varchar(3),ex2) else '' end as ex2, 
	case when tot2 >= 0 then convert(varchar(3),tot2) else '' end as tot2,
	case when dw3 >= 0 then convert(varchar(3),dw3) else '' end as dw3, 
	case when ex3 >= 0 then convert(varchar(3),ex3) else '' end as ex3, 
	case when tot3 >= 0 then convert(varchar(3),tot3) else '' end as tot3,
	case when dw4 >= 0 then convert(varchar(3),dw4) else '' end as dw4, 
	case when ex4 >= 0 then convert(varchar(3),ex4) else '' end as ex4, 
	case when tot4 >= 0 then convert(varchar(3),tot4) else '' end as tot4
into #deelbaan
from loopbaan
where inschrijvingsNr = @iNr

-- De beslissingen

select b.inschrijvingsNr,b.attestcode,b.jaar,b.adviescode,att.attestTekst,adv.adviesTekst 
into #deelbeslissing
from beslis as b
	left join attest as att
	  on b.attestcode = att.attestcode
	left join advies as adv
	  on b.adviescode = adv.adviescode
where b.inschrijvingsNr = @iNr

create table #hulptabel(nr int identity,tekst varchar(5000))

-----------------------------------------------------------
--           XML opbouw
-----------------------------------------------------------

--De hoofding
insert into #hulptabel(tekst)
values('<?xml version="1.0"  encoding="ISO-8859-1" ?>')

insert into #hulptabel(tekst)
select '<loopbaan iNr="' + @iNr + '">'
	+ '<lln>' +  leerlingen.dbo.WEB_XML(isnull(l.voornaam,'')) +  ' '  
	+  leerlingen.dbo.WEB_XML(isnull(l.achternaam,'')) + '</lln>' 
	+ '<geb>' + isNull(convert(varchar(15),a.geboortedatum,103),'') + '</geb>'
 from leerlingen..leerlingen as l
 left join leerlingen..administratieveGegevens as a
	on a.inschrijvingsNr = l.inschrijvingsNr
 where l.inschrijvingsNr = @iNr

-- De jaren

declare @aantalPeriodes char(1)

declare @jaar char(4)
declare jaarCursor cursor local for
  select distinct jaar from #deelbaan
  where inschrijvingsNr=@iNr
  order by jaar
for read only

open jaarCursor
fetch next from jaarCursor into @jaar
while (@@fetch_status=0  )
  begin
    if exists(select * from #deelbaan where 
	inschrijvingsNr = @iNr 
	and jaar = @jaar
	and ((dw2 > 0) or (ex2>0) or (tot2>0))) set @aantalPeriodes = '3' 
    else set @aantalPeriodes = '2'

    insert into #hulptabel(tekst)
    values('<res jaar="' + @jaar + '" aantalPer="' + @aantalPeriodes + '">')

    insert into #hulptabel(tekst)
    select distinct '<klas>' + klas + '</klas>'
    from #deelbaan where jaar = @jaar	

    if @aantalPeriodes = '3'
      begin
	insert into #hulptabel(tekst)
    	select '<vak volgnr="' + isnull(l.vakvolg,'') + '" code="' +  l.vakcode + 
	  '" leraar="' + isnull(g.lk_ident,'') + '" ' + isNull(t.tekst,'') + '>' +
	  '<dw><per nr="1">' + l.dw1 + '</per>' +
	      '<per nr="2">' + l.dw2 + '</per>' +
	      '<per nr="3">' + l.dw3 + '</per>' +
	      '<per nr="t">' + l.dw4 + '</per></dw>' +
	  '<ex><per nr="1">' + l.ex1 + '</per>' +
	      '<per nr="2">' + l.ex2 + '</per>' +
	      '<per nr="3">' + l.ex3 + '</per>' +
	      '<per nr="t">' + l.ex4 + '</per></ex>' +
	  '<tot><per nr="1">' + l.tot1 + '</per>' +
	      '<per nr="2">' + l.tot2 + '</per>' +
	      '<per nr="3">' + l.tot3 + '</per>' +
	      '<per nr="t">' + l.tot4 + '</per></tot>' +
	  '<klasgem><per nr="1">' + 
		case when gem_tot1 >= 0 then convert(varchar(3),gem_tot1) else '' end + 
		'</per><per nr="2">' + 
		case when gem_tot2 >= 0 then convert(varchar(3),gem_tot2) else '' end + 
		'</per><per nr="3">' + 
		case when gem_tot3 >= 0 then convert(varchar(3),gem_tot3) else '' end + 
		'</per><per nr="t">' + 
		case when gem_tot4 >= 0 then convert(varchar(3),gem_tot4) else '' end + 
		'</per>' +
	  '</klasgem></vak>'
	from #deelbaan as l
	left join gemiddelde as g
	  on g.vakcode = l.vakcode
		and l.jaar = g.jaar
		and l.klas = g.klas
	left join #taakTest as t
	  on l.vakcode = t.vak
		and l.jaar = t.jaar
	  where l.jaar = @jaar
	order by l.vakvolg
      end
    else
      begin
	insert into #hulptabel(tekst)
    	select '<vak volgnr="' + isnull(l.vakvolg,'') + '" code="' +  l.vakcode + 
	  '" leraar="' + isnull(g.lk_ident,'') + '" ' + isNull(t.tekst,'') + '>' +
	  '<dw><per nr="1">' + l.dw1+ '</per>' +
	      '<per nr="2">' + l.dw3+ '</per>' +
	      '<per nr="t">' + l.dw4+ '</per></dw>' +
	  '<ex><per nr="1">' + l.ex1 + '</per>' +
	      '<per nr="2">' + l.ex3 + '</per>' +
	      '<per nr="t">' + l.ex4 + '</per></ex>' +
	  '<tot><per nr="1">' + l.tot1 + '</per>' +
	      '<per nr="2">' + l.tot3 + '</per>' +
    	      '<per nr="t">' + l.tot4 + '</per></tot>' +
	  '<klasgem><per nr="1">' + 
		case when gem_tot1 >= 0 then convert(varchar(3),gem_tot1) else '' end + 
		'</per><per nr="2">' + 
		case when gem_tot3 >= 0 then convert(varchar(3),gem_tot3) else '' end + 
		'</per><per nr="t">' + 
		case when gem_tot4 >= 0 then convert(varchar(3),gem_tot4) else '' end + 
		'</per>' +
	  '</klasgem></vak>'
	from #deelbaan as l
	left join gemiddelde as g
	  on g.vakcode = l.vakcode
		and l.jaar = g.jaar
		and l.klas = g.klas
	left join #taakTest as t
	  on l.vakcode = t.vak
		and l.jaar = t.jaar
	  where l.jaar = @jaar
	order by l.vakvolg
      end

    insert into #hulptabel(tekst)
    values ('<beslissing>')
    insert into #hulptabel
    select '<attest code="' + isNull(attestCode,'') + '">' +
	leerlingen.dbo.WEB_XML(isnull(convert(varchar(8000),attestTekst),'')) +
	'</attest><advies code="' + isNull(adviescode,'') + '">' +
	leerlingen.dbo.WEB_XML(isnull(convert(varchar(8000),adviestekst),'')) +
	'</advies>'
    from #deelbeslissing where jaar = @jaar
   
    insert into #hulptabel(tekst)
    select '<test aard="Resultaat overgangstest" vak="' + vak + '">' + isNull(convert(varchar(3),resultaat),'') + '</test>'
	from #deelresultaatOvergang
	where jaar = @jaar

    insert into #hulptabel(tekst)
    select '<test aard="Resultaat taak" vak="' + vak + '">' + isNull(convert(varchar(3),resTaak),'') + '</test>' +
	'<test aard="Resultaat test" vak="' + vak + '">' + isNull(convert(varchar(3),resTest),'') + '</test>'
	from #deelresultaatTaak
	where jaar = @jaar

    insert into #hulptabel(tekst)
    select '<test aard="Resultaat bijkomende test" vak="' + vak + '">' + isNull(convert(varchar(3),resultaat),'') + '</test>'
	from #deelresultaatBijkomend
	where jaar = @jaar

    insert into #hulptabel(tekst)
    select '<notule>' + leerlingen.dbo.WEB_XML(isNull(convert(varchar(8000),notuletekst),'')) + 
	'</notule>'
	from notulen
	where inschrijvingsNr = @iNr and jaar = @jaar


    insert into #hulptabel(tekst)
    select '<amendement>' + leerlingen.dbo.WEB_XML(isNull(convert(varchar(8000),amendementtekst),'')) + 
	'</amendement>'
	from amendement
	where inschrijvingsNr = @iNr and jaar = @jaar

    insert into #hulptabel(tekst)
    values('</beslissing>')


    insert into #hulptabel(tekst)
    values('</res>')
    fetch next from jaarCursor into @jaar
  end
close jaarcursor
deallocate jaarcursor
--De sluiting
insert into #hulptabel(tekst)
values('</loopbaan>')

select * from #hulptabel
order by nr
end